/****** Object:  Stored Procedure ShippingStatusReportGetList    Script Date: Friday, January 04, 2008 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spShippingStatusReportGetList]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spShippingStatusReportGetList]
GO

	
	
	
/******************************************************************************
**		File: 
**		Name: spShippingStatusReportGetList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 1/4/2008 3:57:06 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
CREATE PROCEDURE spShippingStatusReportGetList
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #TempShippingStatusReport (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	ID int	
)

DECLARE @sql nvarchar(2000)

-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #TempShippingStatusReport ([ID]) SELECT '
IF (@PageSize > 0)
	SET @sql = @sql + ' TOP ' + CAST(@Page*@PageSize as nvarchar) 

SET @sql = @sql + ' [ID] FROM [ShippingStatusReport]  ORDER BY [' + @OrderBy + N'] ' + @OrderDirection

EXEC (@sql)

SELECT @TotalRecords = COUNT(*) FROM [ShippingStatusReport]

SELECT
	[ShippingStatusReport].[ID],
	[REQ],
	[OrderDate],
	[ContainerSize],
	[LP],
	[PONo_Office],
	[PONo_Type],
	[PONo_Supplier],
	[PONo_Number],
	[IsPrimary],
	[PONo_Consolidation],
	[AccNo],
	[CustomerOrLocation],
	[FinalDestination],
	[Description],
	[FactoryETD],
	[ShippingDate],
	[ShippingLine],
	[Vessel],
	[ContainerNo],
	[ETA],
	[Week],
	[ArrivalPort],
	[PI],
	[Payment],
	[DocReceived],
	[CompletedDate],
	[BulkType],
	[ModifiedDate],
	[BulkOrderNo],
	[PayAuth],
	[ContainerDeliveried],
	[POInOpera],
	[InvValue],
	[PayDate],
	[Remarks],
	[SInvoiced],
	[SuppOrderNo],
	[OfficeID],
	[CustPONo],
	[OnHold],
	[BulkOrderOfficeID],
	[SInvoicedPd],
	[Catalogue],
	[ShippingLineID],
	[CatalogID],
	[PDepValue],
	[SalesInvoiceValue],
	[SalesInvoiceDate],
	[SalesPaymentDue],
	[SalesOrder],
	[TargetETD],
	[IsArchived],
	[ResponsibleID],
	[FactoryETDEnteredDate]
FROM
	#TempShippingStatusReport AS tblTemp JOIN [ShippingStatusReport] ON
	tblTemp.ID = [ShippingStatusReport].ID 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
ORDER BY RowNumber

DROP TABLE #TempShippingStatusReport

GO

	
